package com.bjfu.mapper;

import com.bjfu.model.UserInfo;
import com.bjfu.model.UserWithInfo;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface UserInfoMapper {

    // 插入新的用户信息
    @Insert("INSERT INTO user_info (user_id, team_id, name, avatar_url, research_field, status, bio, apply_reason,email) " +
            "VALUES (#{userId}, #{teamId}, #{name}, #{avatarUrl}, #{researchField}, #{status}, #{bio}, #{applyReason},#{email})")
    @Options(useGeneratedKeys = true, keyProperty = "userId", keyColumn = "user_id")
    int insertUserInfo(UserInfo userInfo);


    // 根据用户ID查询用户信息
    @Select("SELECT * FROM user_info WHERE user_id = #{userId}")
    UserInfo getUserInfoById(Long userId);
    //查询所有团队管理员信息


    // 查询所有用户信息
    @Select("SELECT user_id, team_id, name, avatar_url, research_field, status, bio, apply_reason,email " +
            "FROM user_info")
    List<UserInfo> getAllUserInfos();

    // 更新用户信息
    @Update("UPDATE user_info SET team_id = #{teamId}, name = #{name}, avatar_url = #{avatarUrl}, " +
            "research_field = #{researchField}, status = #{status}, bio = #{bio}, apply_reason = #{applyReason} ,email = #{email} " +
            "WHERE user_id = #{userId}")
    int updateUserInfo(UserInfo userInfo);
//团队成员更改个人信息
    @Update("UPDATE user_info SET name = #{name}, research_field = #{researchField}, bio = #{bio}, email = #{email}WHERE user_id = #{userId} ")
    int updateUserInfoByMember(UserInfo userInfo);


    // 根据用户ID删除用户信息
    @Delete("DELETE FROM user_info WHERE user_id = #{userId}")
    int deleteUserInfo(Long userId);

    // 查询用户信息根据状态
    @Select("SELECT user_id, team_id, name, avatar_url, research_field, status, bio, apply_reason,email " +
            "FROM user_info WHERE status = #{status}")
    List<UserInfo> getUserInfoByStatus(Integer status);

    /*// 查询所有待审核成员信息
    @Select("SELECT * FROM user_info WHERE status = 1 ")
    List<UserInfo> queryPendingMembers();*/
    @Select("SELECT ui.* FROM user_info ui " +
            "JOIN user u ON ui.user_id = u.user_id " +
            "WHERE ui.status = 1 AND u.role =2 ")
    List<UserInfo> queryPendingMembers();

    //根据user_id查询审核状态
    @Select("SELECT status FROM user_info WHERE user_id = #{user_id}")
    String getStatusByUserId(Long user_id);

    // 根据用户名查询用户信息
    @Select("SELECT * FROM user_info WHERE name = #{name}")
    UserInfo getUserInfoByName(String name);


    //审核团队成员信息
    @Update("UPDATE user_info SET status = #{pass_status} WHERE user_id = #{user_id}")
    int judgeTeamMemberInfo(Long user_id, Integer pass_status);

    // 查询 status 为 4 的用户信息（需要被注销的用户）
    @Select("SELECT ui.* FROM user_info ui " +
            "JOIN user u ON ui.user_id = u.user_id " +
            "WHERE u.status = 4 AND role=3")
    List<UserInfo> getUsersNeedCancel();

    // 查询指定邮箱的用户信息
    @Select("SELECT * FROM user_info WHERE email = #{email}")
    UserInfo getUserInfoByEmail(String email);

    // 查询 status 为 0和4 的用户信息（待审核的用户）
    @Select("SELECT ui.* FROM user_info ui " +
            "JOIN user u ON ui.user_id = u.user_id " +
            "WHERE u.status = 0 AND role =3 ")
    List<UserInfo> getWaitJudgeUsers();

    @Select("SELECT u.user_id AS user_id, u.username AS username, u.password AS password, u.role AS role, " +
            "u.registration_time AS register_time, u.status AS status, u.permit AS permit, " +  // 修正拼写错误
            "ui.name AS name, ui.avatar_url AS avatar_url, ui.research_field AS research_field, " +
            "ui.status AS user_info_status, ui.bio AS bio, ui.apply_reason AS apply_reason, ui.email AS email " +
            "FROM user_info ui " +
            "JOIN user u ON ui.user_id = u.user_id " +
            "WHERE (u.role = 2 OR u.role = 3) " +  // 角色为2或3
            "AND u.status IN (1, 2, 4)")                  // 用户状态为已激活（审核通过） add:可以是已激活，审核失败，被注销
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property = "role", column = "role"),
            @Result(property = "registrationTime", column = "registration_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "permit", column = "permit"),

            // user_info 表字段映射到 UserWithInfo 类的属性
            @Result(property = "name", column = "name"),
            @Result(property = "avatarUrl", column = "avatar_url"),
            @Result(property = "researchField", column = "research_field"),
            @Result(property = "userInfoStatus", column = "user_info_status"),  // 映射到 user_info_status
            @Result(property = "bio", column = "bio"),
            @Result(property = "applyReason", column = "apply_reason"),
            @Result(property = "email", column = "email")
    })
    List<UserWithInfo> getCouldcancelledUser();

   /* @Select("SELECT ui.* " +
            "FROM user_info ui " +
            "JOIN user u ON ui.user_id = u.user_id " +
            "WHERE (u.role = 2 OR u.role = 3) " +  // 角色为2或3
            "AND u.status = 0")                  // 用户状态为未激活（审核通过）
    List<UserInfo> getRegisteringUser();*/
   @Select("SELECT u.user_id AS user_id, u.username AS username, u.password AS password, u.role AS role, " +
           "u.registration_time AS register_time, u.status AS status, u.permit AS permit, " +  // 修正拼写错误
           "ui.name AS name, ui.avatar_url AS avatar_url, ui.research_field AS research_field, " +
           "ui.status AS user_info_status, ui.bio AS bio, ui.apply_reason AS apply_reason, ui.email AS email " +
           "FROM user_info ui " +
           "JOIN user u ON ui.user_id = u.user_id " +
           "WHERE (u.role = 2 OR u.role = 3) " +  // 角色为2或3
           "AND u.status = 0")                  // 用户状态为未激活（审核通过）
   @Results({
           @Result(property = "userId", column = "user_id"),
           @Result(property = "username", column = "username"),
           @Result(property = "password", column = "password"),
           @Result(property = "role", column = "role"),
           @Result(property = "registrationTime", column = "registration_time"),
           @Result(property = "status", column = "status"),
           @Result(property = "permit", column = "permit"),

           // user_info 表字段映射到 UserWithInfo 类的属性
           @Result(property = "name", column = "name"),
           @Result(property = "avatarUrl", column = "avatar_url"),
           @Result(property = "researchField", column = "research_field"),
           @Result(property = "userInfoStatus", column = "user_info_status"),  // 映射到 user_info_status
           @Result(property = "bio", column = "bio"),
           @Result(property = "applyReason", column = "apply_reason"),
           @Result(property = "email", column = "email")
   })
   List<UserWithInfo> getRegisteringUserWithInfo();





    @Select("SELECT u.user_id AS user_id, u.username AS username, u.password AS password, u.role AS role, " +
            "u.registration_time AS register_time, u.status AS status, u.permit AS permit, " +  // 修正拼写错误
            "ui.name AS name, ui.avatar_url AS avatar_url, ui.research_field AS research_field, " +
            "ui.status AS user_info_status, ui.bio AS bio, ui.apply_reason AS apply_reason, ui.email AS email " +
            "FROM user_info ui " +
            "JOIN user u ON ui.user_id = u.user_id " +
            "WHERE (u.role = 2 OR u.role = 3) " )                // 角色为2或3
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property = "role", column = "role"),
            @Result(property = "registrationTime", column = "registration_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "permit", column = "permit"),

            // user_info 表字段映射到 UserWithInfo 类的属性
            @Result(property = "name", column = "name"),
            @Result(property = "avatarUrl", column = "avatar_url"),
            @Result(property = "researchField", column = "research_field"),
            @Result(property = "userInfoStatus", column = "user_info_status"),  // 映射到 user_info_status
            @Result(property = "bio", column = "bio"),
            @Result(property = "applyReason", column = "apply_reason"),
            @Result(property = "email", column = "email")
    })
    List<UserWithInfo> getMemberAndUserInfo();
}
